IF OBJECT_ID('ssf.dpnd_derive_object_summary') IS NOT NULL
	DROP PROCEDURE ssf.dpnd_derive_object_summary
GO

CREATE PROCEDURE ssf.dpnd_derive_object_summary
	@object_name NVARCHAR(256)
	, @object_summary XML = NULL OUTPUT
AS
/*
<object_header>

<input_dependency>
	<object_type>VIEW</object_type>
	<dpnd_object_name>INFORMATION_SCHEMA.ROUTINES</dpnd_object_name>
</input_dependency>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @sql VARCHAR(8000) -- or (MAX)
			, @nsql NVARCHAR(4000) -- or (MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@object_schema_name NVARCHAR(128)
			, @object_specific_name NVARCHAR(128)
-- ============

-- ============
/*<code_comment><![CDATA[initialize output parameter]]></code_comment>*/
SET @object_summary = NULL
-- ============

-- ============
/*<code_comment><![CDATA[exit if null, blank, or non-existent object name]]></code_comment>*/
IF NULLIF(@object_name, N'') IS NULL
OR OBJECT_ID(@object_name) IS NULL
	RETURN @@ERROR
-- ============

-- ============
/*<code_comment><![CDATA[parse object name]]></code_comment>*/
SET @object_schema_name = OBJECT_SCHEMA_NAME(OBJECT_ID(@object_name))
SET @object_specific_name = PARSENAME(@object_name, 1)
-- ============

-- ============
/*<code_comment><![CDATA[derive object summary]]></code_comment>*/
DECLARE @object_summary_table TABLE (
	dpnd_object_name NVARCHAR(512)
	, object_type NVARCHAR(128)
	, return_data_type NVARCHAR(128)
)

INSERT @object_summary_table
SELECT ISNULL(SPECIFIC_SCHEMA + '.', '') + SPECIFIC_NAME AS dpnd_object_name
	, ROUTINE_TYPE AS object_type
	, CASE WHEN ROUTINE_TYPE = 'PROCEDURE'
		THEN 'INT'
		WHEN DATA_TYPE LIKE '%CHAR' OR DATA_TYPE LIKE '%BINARY'
		THEN UPPER(DATA_TYPE) + '(' + ISNULL(NULLIF(CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR), '-1'), 'MAX') + ')'
		WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL')
		THEN UPPER(DATA_TYPE) + '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
		WHEN DATA_TYPE IN ('FLOAT', 'REAL')
		THEN UPPER(DATA_TYPE) + '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ')'
		WHEN DATA_TYPE IN ('DATETIME2', 'TIME')
		THEN UPPER(DATA_TYPE) + '(' + CAST(DATETIME_PRECISION AS VARCHAR) + ')'
		ELSE UPPER(DATA_TYPE)
		END AS return_data_type
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = @object_schema_name
AND SPECIFIC_NAME = @object_specific_name

SELECT @sql_error = @@ERROR
	, @row_count = @@ROWCOUNT

IF @sql_error <> 0
BEGIN
	RAISERROR('The INSERT into the table <@object_summary_table> failed.', 16, 1)
	RETURN @sql_error
END
-- ============

-- ============
/*<code_comment><![CDATA[construct xml output]]></code_comment>*/
BEGIN TRY
	SET @object_summary = (
		SELECT dpnd_object_name, object_type, CAST(N'' AS NVARCHAR(MAX)) AS purpose, return_data_type
		FROM @object_summary_table
		FOR XML RAW ('object_summary'), ELEMENTS, TYPE
	)
END TRY
BEGIN CATCH
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
	
	RAISERROR('Failed trying to construct xml output:%s%s', 16, 1, @crlf, @error_message)
	RETURN @sql_error
END CATCH
-- ============

RETURN @@ERROR
GO
